
package com.itstyle.jpa.repository;

import java.util.List;

import com.itstyle.jpa.entity.UserAndClassModel;
import com.itstyle.jpa.model.Student;
import com.querydsl.core.QueryResults;
import com.querydsl.core.Tuple;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.itstyle.jpa.model.User;
/**
 * 数据操作层
 * 创建者 科帮网
 * 创建时间	2017年7月25日
 *
 */
public interface UserRepository extends JpaRepository<User, Long> {

	User findByName(String name);
	
	User findByAge(Integer age);

	User findByNameAndAge(String name, Integer age);
	
	List<User> findByNameLike(String name);

	@Query("from User u where u.name=:name")
	User findUser(@Param("name") String name);

	String count = "select" +
			"count(*) " +
			"from  app_student s  LEFT JOIN app_class c on s.class_id = c.id";
	String baseSql = "select" +
			"  s.id as id," +
			"  s.name as name," +
			"  s.age as age," +
			"  c.id as classId," +
			"  c.class_name as className," +
			"  c.teacher_name as teacherName " +
			"from  app_student s  LEFT JOIN app_class c on s.class_id = c.id ORDER BY ?#{#pageable}";
	@Query(
			nativeQuery = true,
			countQuery = count,
			value = baseSql
	)
	Page<Object> queryUsers(Pageable pageable);

	@Query("select" +
			"  s.id as studentId," +
			"  s.name as studentName," +
			"  s.age as studentAge," +
			"  c.id as classId," +
			"  c.className as className," +
			"  c.teacherName as teacherName " +
			"from  AppStudent s , AppClass c where s.id = c.id")
	Page<UserAndClassModel> getUserAndClassModel(Pageable pageable);



}
